using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using BlobNet.Base.Collections.List;
using BlobNet.Base.Enums;

namespace BlobNet.DefaultDataProviders.Base
{
    [Serializable]
    public abstract class KVSDataProviderBase
    {
        protected abstract string ConnectionString { get; }
        protected abstract int BinaryFieldMaxSize { get; }

        protected abstract void ClearResources();

        public void CreateSchema(Guid uniqueIdentifier)
        {
            using (IDbConnection conn = NewConnection())
            {
                conn.Open();
                CreateDataProviderObjects(conn, uniqueIdentifier);

                conn.Close();
            }
            ClearResources();
        }

        public void RemoveSchema(Guid uniqueIdentifier)
        {
            using (IDbConnection conn = NewConnection())
            {
                conn.Open();
                RemoveDataProviderObjects(conn, uniqueIdentifier);

                conn.Close();
            }
            ClearResources();
        }

        protected byte[] GetObjectBytes(object value)
        {
            if (value == null)
                return null;

            byte[] result = null;
            using (var memStream = new MemoryStream())
            {
                var bf = new BinaryFormatter();
                bf.Serialize(memStream, value);
                result = memStream.ToArray();
            }
            return result;
        }

        protected static object ReadBytes(IDataReader reader, int columnPos)
        {
            object result = null;
            int bufferSize = 4096;
            var outbyte = new byte[bufferSize];
            long retval;
            long startIndex = 0;


            using (var memStream = new MemoryStream())
            {
                using (var bw = new BinaryWriter(memStream))
                {
                    try
                    {
                        retval = reader.GetBytes(columnPos, startIndex, outbyte, 0, bufferSize);
                    }
                    catch
                    {
                        //TODO: ELIMINAR EL CONTROL CON LA EXCEPCION antes de mergear
                        return null;
                    }

                    while (retval == bufferSize)
                    {
                        bw.Write(outbyte);
                        bw.Flush();

                        startIndex += bufferSize;
                        retval = reader.GetBytes(columnPos, startIndex, outbyte, 0, bufferSize);
                    }

                    bw.Write(outbyte, 0, (int) retval);
                    bw.Flush();


                    var bf = new BinaryFormatter();
                    memStream.Position = 0;
                    result = bf.Deserialize(memStream);
                }
            }

            return result;
        }

        protected abstract void AddParameter(object value, IDbCommand command, string paramName, DbType fieldType,
                                             ParameterDirection parameterDirection);

        protected abstract void AddParameter(object value, IDbCommand command, string paramName, DbType fieldType,
                                             ParameterDirection parameterDirection, int size);

        protected abstract IDbConnection NewConnection();

        public void AddPropertyValue<TValue>(Guid domainId, Guid ownerId, string propertyName,
                                             PropertyValueType propertyValueType, string fullyQualifiedType,
                                             TValue value)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "AddBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;
                    AddParameter(ownerId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(propertyName, cmd, "@PropertyName", DbType.String,
                                 ParameterDirection.Input, 100);
                    AddParameter(((int) propertyValueType).ToString(), cmd, "@ValueType", DbType.String,
                                 ParameterDirection.Input, 1);

                    AddParameter(fullyQualifiedType, cmd, "@FullyQualifiedType", DbType.String,
                                 ParameterDirection.Input, 512);

                    AddParameter(value != null ? (object) GetObjectBytes(value) : DBNull.Value, cmd, "@Value",
                                 DbType.Binary,
                                 ParameterDirection.Input, BinaryFieldMaxSize);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        protected static string GetBlobDomainObjectName(Guid blobDomainId, string genericName)
        {
            return string.Concat(GetBlobDomainPrefix(blobDomainId), genericName);
        }

        protected static string GetBlobDomainPrefix(Guid blobDomainId)
        {
            return "X" + blobDomainId.ToString().Replace('-', '_');
        }

        public void UpdatePropertyValue<TValue>(Guid domainId, Guid ownerId, string propertyName,
                                                PropertyValueType propertyValueType, string fullyQualifiedType,
                                                TValue value)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "UpdateBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(ownerId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(propertyName, cmd, "@PropertyName", DbType.String,
                                 ParameterDirection.Input, 100);
                    AddParameter(((int) propertyValueType).ToString(), cmd, "@ValueType", DbType.String,
                                 ParameterDirection.Input, 1);
                    AddParameter(fullyQualifiedType, cmd, "@FullyQualifiedType", DbType.String,
                                 ParameterDirection.Input, 512);
                    AddParameter(value != null ? (object) GetObjectBytes(value) : DBNull.Value, cmd, "@Value",
                                 DbType.Binary,
                                 ParameterDirection.Input, BinaryFieldMaxSize);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public TValue RetrievePropertyValue<TValue>(Guid domainId, Guid ownerId, string propertyName)
        {
            TValue result;
            object queryResult = null;
            PropertyValueType valueType;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "SelectValueBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(ownerId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(propertyName, cmd, "@PropertyName", DbType.String,
                                 ParameterDirection.Input, 100);


                    conn.Open();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        if (reader.Read())
                        {
                            valueType =
                                (PropertyValueType) Enum.Parse(typeof (PropertyValueType), (string) reader[0]);

                            queryResult = ReadBytes(reader, 1);
                        }
                        else
                        {
                            throw new DataException("Not found");
                        }
                    }
                }
                conn.Close();
            }

            if (queryResult == DBNull.Value)
                result = default(TValue);

            else
            {
                switch (valueType)
                {
                    case PropertyValueType.Enumeration:
                        result = (TValue) Enum.Parse(typeof (TValue), (string) queryResult);
                        break;
                    case PropertyValueType.Value:
                        result = (TValue) Convert.ChangeType(queryResult, typeof (TValue));
                        break;
                    case PropertyValueType.Entity:
                        result = (TValue) queryResult;
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }
            }

            return result;
        }

        public void RemovePropertyValues(Guid domainId, Guid entityId)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "DeleteEntityBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(entityId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public bool EntityIsReferenced(Guid domainId, Guid entityId)
        {
            bool result;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "BlobPartReferenced");
                    cmd.CommandType = CommandType.StoredProcedure;

                    byte[] objectBytes = GetObjectBytes(entityId);

                    AddParameter(objectBytes, cmd, "@EntityIdBin", DbType.Binary,
                                 ParameterDirection.Input, BinaryFieldMaxSize);
                    conn.Open();
                    object executeScalar = cmd.ExecuteScalar();
                    result = Convert.ToBoolean(executeScalar);
                }
                conn.Close();
            }

            return result;
        }

        public bool HasPropertyValue(Guid domainId, Guid entityId, string propertyName)
        {
            bool result;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "HasValueBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;


                    AddParameter(entityId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(propertyName, cmd, "@PropertyName", DbType.String,
                                 ParameterDirection.Input, 100);

                    conn.Open();
                    object executeScalar = cmd.ExecuteScalar();
                    result = Convert.ToBoolean(executeScalar);
                }
                conn.Close();
            }

            return result;
        }

        public IEnumerable<string> RetrieveRegisteredProperties(Guid domainId, Guid entityId)
        {
            throw new NotImplementedException();
        }

        public bool ExistsBlobDomain(Guid entityId)
        {
            bool result;

            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    string databaseName = ConfigurationManager.AppSettings["DataProviderName"];
                    cmd.CommandText = string.Format("SELECT count(1) FROM {0}.sys.tables WHERE NAME = N'{1}'",
                                                    databaseName, GetBlobDomainObjectName(entityId, "BlobStatusTable"));
                    conn.Open();
                    result = Convert.ToBoolean(cmd.ExecuteScalar());
                }
                conn.Close();
            }

            ClearResources();

            return result;
        }

        public List<Guid> RetrieveItemsIds<TType>(Guid domainId)
        {
            var result = new List<Guid>();

            PropertyValueType valueType;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "SelectItemsOfTypeBlobStatusTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(typeof (TType).FullName, cmd, "@FullyQualifiedType", DbType.String,
                                 ParameterDirection.Input, 512);

                    conn.Open();

                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        while (reader.Read())
                        {
                            //string s = reader[0].ToString();
                            //if (s == typeof (TType).FullName) //TODO: improve query and remove this
                            //{
                            object readBytes = ReadBytes(reader, 0);
                            result.Add((Guid) readBytes);
                            //}
                        }
                    }
                }
                conn.Close();
            }


            return result;
        }


        public void AddEntityToCollection(Guid domainId, Guid collectionId, int position, string fullyQualifiedType,
                                          object value)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "AddBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;
                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(position, cmd, "@Position", DbType.Int32, ParameterDirection.Input);
                    AddParameter(GetObjectBytes(value), cmd, "@Value", DbType.Binary,
                                 ParameterDirection.Input, BinaryFieldMaxSize);
                    AddParameter(fullyQualifiedType, cmd, "@FullyQualifiedType", DbType.String,
                                 ParameterDirection.Input, BinaryFieldMaxSize);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void UpdateEntityToCollection(Guid domainId, Guid collectionId, int position,
                                             string fullyQualifiedType, object value)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "UpdateBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(position, cmd, "@Position", DbType.Int32, ParameterDirection.Input);
                    AddParameter(GetObjectBytes(value), cmd, "@Value", DbType.Binary,
                                 ParameterDirection.Input, BinaryFieldMaxSize);
                    AddParameter(fullyQualifiedType, cmd, "@FullyQualifiedType", DbType.String,
                                 ParameterDirection.Input, BinaryFieldMaxSize);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void RemoveEntityFromCollection(Guid domainId, Guid collectionId, int position)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "DeleteBlobPartsListTableEntity");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(position, cmd, "@Position", DbType.Int32, ParameterDirection.Input);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public int GetCollectionCount(Guid domainId, Guid collectionId)
        {
            int result;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "CountBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    conn.Open();
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                conn.Close();
            }

            return result;
        }

        public void RemoveCollection(Guid domainId, Guid collectionId)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "DeleteBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public IList<ITupleList> RetrieveListItems(Guid domainId, Guid collectionId)
        {
            var result = new List<ITupleList>();
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "SelectBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    conn.Open();
                    IDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                    while (reader.Read())
                    {
                        //TODO: this can be faster reader
                        var position = (int) reader[0];
                        var tuple = new TupleList(position, collectionId,
                                                  reader[1].ToString(),
                                                  ReadBytes(reader, 2));
                        result.Add(tuple);
                    }
                }
                conn.Close();
            }

            return result;
        }

        public ITupleList RetrieveListItem(Guid domainId, Guid collectionId, int position)
        {
            ITupleList result = null;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "SelectBlobPartsListTableEntity");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(position, cmd, "@Position", DbType.Int32, ParameterDirection.Input);
                    conn.Open();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        if (reader.Read())
                        {
                            var tuple = new TupleList(position, collectionId,
                                                      reader[0].ToString(),
                                                      ReadBytes(reader, 1));
                            result = tuple;
                        }
                    }
                }
                conn.Close();
            }

            return result;
        }

        public ITupleList RetrieveListItem(Guid domainId, Guid collectionId, Guid entityId)
        {
            ITupleList result = null;
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "SelectBlobPartsListTableEntityByEntityId");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(collectionId, cmd, "@Id", DbType.Guid, ParameterDirection.Input);
                    AddParameter(GetObjectBytes(entityId), cmd, "@EntityId", DbType.Binary,
                                 ParameterDirection.Input);
                    conn.Open();
                    IDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                    if (reader.Read())
                    {
                        var tuple = new TupleList((int) reader[0], collectionId,
                                                  reader[1].ToString(),
                                                  ReadBytes(reader, 2));
                        result = tuple;
                    }
                }
                conn.Close();
            }

            return result;
        }

        public void CopyListRange(Guid domainId, Guid sourceCollectionId, Guid destinationCollectionId,
                                  int initialPosition, int numItemsToCopy)
        {
            using (IDbConnection conn = NewConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetBlobDomainObjectName(domainId, "CopyRangeBlobPartsListTable");
                    cmd.CommandType = CommandType.StoredProcedure;

                    AddParameter(sourceCollectionId, cmd, "@SourceCollectionId", DbType.Guid,
                                 ParameterDirection.Input);
                    AddParameter(destinationCollectionId, cmd, "@DestinationCollectionId",
                                 DbType.Guid, ParameterDirection.Input);
                    AddParameter(initialPosition, cmd, "@InitialPosition", DbType.Int32,
                                 ParameterDirection.Input);
                    AddParameter(numItemsToCopy, cmd, "@NumItems", DbType.Int32, ParameterDirection.Input);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        protected void ExecuteChainOfCommands(string[] commands, IDbConnection conn)
        {
            foreach (string command in commands)
            {
                if (command.Length > 0)
                {
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        //TODO: REMOVE THIS
                        try
                        {
                            cmd.CommandText = command;
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
        }

        protected abstract string CreateStorageScriptName { get; }
        protected abstract string RemoveStorageScriptName { get; }


        protected virtual void CreateDataProviderObjects(IDbConnection conn, Guid blobDomainId)
        {
            string[] commands = GetScriptCommands(blobDomainId,
                                                  CreateStorageScriptName);

            ExecuteChainOfCommands(commands, conn);
        }

        protected virtual void RemoveDataProviderObjects(IDbConnection conn, Guid blobDomainId)
        {
            string[] commands = GetScriptCommands(blobDomainId,
                                                  RemoveStorageScriptName);

            ExecuteChainOfCommands(commands, conn);
        }

       
        private string[] GetScriptCommands(Guid blobDomainId, string manifestResName)
        {
            //if (scriptCache == null)
            //{

            string scriptCache;
            using (
                Stream stream =
                    Assembly.GetExecutingAssembly().GetManifestResourceStream(manifestResName))
                //TODO:change to final destination, ask if there is any functionality i should reuse
            {
                using (var streamReader = new StreamReader(stream))
                {
                    scriptCache = streamReader.ReadToEnd();
                }
            }
            //}
            string script = ReplaceScriptKeywords(blobDomainId, scriptCache);
            string[] commands = script.Replace(Environment.NewLine, " ").Replace('\t', ' ').Split(
                new[] {"GO"}, StringSplitOptions.RemoveEmptyEntries);

            return commands;
        }

        protected abstract string ReplaceScriptKeywords(Guid blobDomainId, string scriptString);
    }
}